/**
 * All code in Payments.java created by Karla.
 */

package Classes;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.NumberFormat;
import java.util.Vector;

/**
 * 
 * @author Karla Barber
 * General Payments Class creation
 *
 */

public class Payments {
	//===============================Properties=======================================
	// start Karla Barber properties
	private int pmtID, bookingID;
	private float pmtAmt;
	private String pmtMthd;
	// end Karla Barber properties
	
	//===============================Constructors=======================================
	
	//===============================Getters and Setters================================
	// start Karla Barber getters and setters
	
	public int getPmtID() {
		return pmtID;
	}
	public void setPmtID(int pmtID) {
		this.pmtID = pmtID;
	}
	public int getBookingID() {
		return bookingID;
	}
	public void setBookingID(int bookingID) {
		this.bookingID = bookingID;
	}
	public float getPmtAmt() {
		return pmtAmt;
	}
	public void setPmtAmt(float pmtAmt) {
		this.pmtAmt = pmtAmt;
	}
	public String getPmtMthd() {
		return pmtMthd;
	}
	public void setPmtMthd(String pmtMthd) {
		this.pmtMthd = pmtMthd;
	}
	// end Karla Barber getters and setters
	
	//===============================Methods============================================
	// start Karla Barber methods and overrides
	@Override
	public String toString() {
		NumberFormat nf = NumberFormat.getCurrencyInstance();
		return "" + pmtID + "     " + nf.format(pmtAmt) + "     " + pmtMthd;
	}
	
    public Vector<String> loadPaymentsVector(String sql) 
    {
            Vector v;
            v = new Vector();
            try 
            {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
                    //String lsql = "select * from ICTOOSD.CUSTOMERS ORDER BY CUSTLASTNAME";
                    //String lsql = "select * from ICTOOSD.CUSTOMERS WHERE CUSTOMERID = 135";
                    Statement stmt = dbConn.createStatement();
                    ResultSet rs = stmt.executeQuery(sql);
                    while (rs.next())
                    {
                    	Payments p = new Payments();
                    	p.setBookingID(rs.getInt("BOOKINGID"));
                    	p.setPmtAmt(rs.getFloat("PMT_AMT"));
                    	p.setPmtID(rs.getInt("PAYMENTID"));
                    	p.setPmtMthd(rs.getString("PMT_MTHD"));
                    	v.addElement(p);
                    }
                    dbConn.close();
            } 
            catch (SQLException e) 
            {
                    e.printStackTrace();
            } catch (ClassNotFoundException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            }
            return v;
    }
    
    public void deletePmt()
    {
        try 
        {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
				String lsql = "delete payments where paymentid = " + pmtID;
                			      
                Statement stmt = dbConn.createStatement();
                stmt.execute(lsql);
                dbConn.commit();
                dbConn.close();
        }
        catch (SQLException e) 
        {
                e.printStackTrace();
        } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
        }
    }

    public float getOutstanding(int bookId)
    {
    	float totPrice = 0;
    	float totPmts = 0;
    	float outstanding = 0;
        try 
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
			String lsql = "select sum(baseprice) as TOTPRICE from ictoosd.bookingdetails " +
						  "where bookingid = " + bookId;
            			      
            Statement stmt = dbConn.createStatement();
            ResultSet rs = stmt.executeQuery(lsql);
            while (rs.next())
            {
            	totPrice = rs.getFloat("TOTPRICE");
            }

			lsql = "select sum(PMT_AMT) as TOTPMTS from ictoosd.payments " +
				   "where bookingid = " + bookId;
		      
			  rs = stmt.executeQuery(lsql);
			  while (rs.next())
			  {
				  totPmts = rs.getFloat("TOTPMTS");
			  }
			  outstanding = totPrice - totPmts;
			  dbConn.close();
        }
        catch (SQLException e) 
        {
                e.printStackTrace();
        } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
        }
		return outstanding;
    }
    
    public String getNextPmtId()
    {
    	String nextId = "";
    	try
    	{
    		Class.forName("oracle.jdbc.driver.OracleDriver");
	        Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
			
			if (dbConn != null)
			{
				try {
					//Create a new Statement
					Statement stmnt = dbConn.createStatement();
					ResultSet rs = stmnt.executeQuery("select max(paymentid) as maxId from ictoosd.payments");
					
					
					while (rs.next())
					{
						//grab the last number and add one to it to get next number
						nextId = Integer.toString(rs.getInt("maxId") + 1);
					}
				}
				catch (SQLException e) 
				{
					e.printStackTrace();
				}
				dbConn.close();	
			}
			else
			{
				System.out.println("No SQL connection was made.");
			}
			
			if (nextId == "")
			{
				nextId = "1";
			}
			return nextId;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    	}
		return nextId;
    }

	public void savePmt()
    {
        try 
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
            String lsql = "insert into ICTOOSD.PAYMENTS" +
            			  "(PAYMENTID, BOOKINGID, PMT_AMT, PMT_MTHD) " +
            			  "Values(?,?,?,?)";
                			      
	        PreparedStatement addStmnt = dbConn.prepareStatement(lsql);
		       
	        addStmnt.setInt(1, pmtID);
	        addStmnt.setInt(2, bookingID);
	        addStmnt.setFloat(3, pmtAmt);
	        addStmnt.setString(4, pmtMthd);
	        
	        int rowsChanged = addStmnt.executeUpdate();
	        
	        if (rowsChanged == 0)
	        {
	        	System.out.println("update failed");
	        }
	        else
	        {
	        	System.out.println("updated " + rowsChanged + " row(s)");
	        }
	        dbConn.commit();
	        dbConn.close();
        }
        catch (SQLException e) 
        {
                e.printStackTrace();
        } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
        }
    }
}
